Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient

Public Class DRole

    Public Shared Function GetPrivileges(ByVal PrivNm As String) As Integer
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT PRID FROM spriv WHERE PRNM = '" & PrivNm & "'"
            Return DbHelper.ExecuteScalar(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetPrivileges")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetPrivileges() As DataTable
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT PRID, PRNM FROM spriv "
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetPrivileges")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetRoles() As DataTable
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT RoleID, Name FROM mroles "
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetPrivileges")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetUserRole() As DataTable
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT UserID,RoleID FROM mpuser "

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetUserRole")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetUserRole(ByVal UserID As String) As DataTable
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT UserID,RoleID FROM mpuser WHERE UserID = '" & UserID & "'"
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetUserRole")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetSearchedItems(ByVal RoleID As Integer) As DataTable
        Dim strSql As String = String.Empty
        Try
            strSql = "SELECT rp.RoleID, rp.PrID, p.PRNM, r.Name FROM spriv p, mrolepriv rp, mroles r " & _
                     "WHERE rp.PrID=p.PrId AND rp.RoleID = r.RoleID AND r.RoleID = '" & RoleID & "'"
            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetPrivileges")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetRolePriv(ByVal userID As String) As DataTable
        Dim strSql As String = String.Empty
        Try
           
            strSql = "SELECT rp.PrID FROM spriv p, mrolepriv rp, mroles r " & _
                     "WHERE rp.PrID=p.PrId and rp.RoleID = r.RoleID AND rp.RoleID =(SELECT RoleID FROM mpuser WHERE UserID = '" & userID & "')"

            Return DbHelper.ExecuteDataTable(strSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "GetRolePriv")
            Return Nothing
        End Try
    End Function

    Public Shared Function InsertRoles(ByVal SetPriv() As Integer, ByVal SaveUpdate As String, ByVal role As CRole) As Boolean
        Dim sql As String
        Dim dbConnection As New dbAccess
        Dim param(3) As SqlParameter
        Dim intResult As Integer

        Try
            dbConnection.BeginTransaction()

            If SaveUpdate = "S" Then

                sql = "INSERT INTO mRoles(RoleID, Name, LUSR, LUDT) " & _
                      "VALUES(@RoleID,@Name,@LUSR,@LUDT)"

                role.RoleID = DGeneral.GetSeqNum("Role", dbConnection)
                param(0) = New SqlParameter("@RoleID", role.RoleID)
                param(1) = New SqlParameter("@Name", role.RoleName)
                param(2) = New SqlParameter("@LUSR", CCommon.GetUserID)
                param(3) = New SqlParameter("@LUDT", Date.Now())
                
                If dbConnection.ExecuteNonQuery(sql, param) = 0 Then
                    intResult = 0
                Else
                    intResult = InsertPrivileges(SetPriv, "S", role.RoleID, dbConnection)
                End If
            Else
                sql = " UPDATE mRoles SET Name = @Name, LUSR = @LUSR, LUDT = @LUDT WHERE RoleID = @RoleID"

                param(0) = New SqlParameter("@RoleID", role.RoleID)
                param(1) = New SqlParameter("@Name", role.RoleName)
                param(2) = New SqlParameter("@LUSR", CCommon.GetUserID)
                param(3) = New SqlParameter("@LUDT", Date.Now())

                If dbConnection.ExecuteNonQuery(sql, param) = 0 Then
                    intResult = 0
                Else
                    intResult = InsertPrivileges(SetPriv, "U", role.RoleID, dbConnection)
                End If
            End If
            If intResult = 0 Then
                Exit Function
            Else
                dbConnection.CommitTransaction()
                Return True
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "InsertRoles")
            dbConnection.RollBackTransaction()
            Return False
        Finally
            dbConnection.closeSqlcon()
        End Try
    End Function

    Public Shared Function InsertPrivileges(ByRef priv() As Integer, ByVal SaveUpdate As String, ByVal roleid As Integer, ByVal dbConnection As dbAccess) As Integer
        Dim sql As String
        Dim param(3) As SqlParameter
        Dim intResult As Integer
        Try

            If SaveUpdate <> "S" Then
                sql = "DELETE FROM mRolePriv WHERE RoleID = " & roleid
                dbConnection.ExecuteNonQuery(sql)
            End If
            sql = "INSERT INTO mRolePriv(RoleID, PrID, LU, LD) " & _
                  "VALUES(@RoleID,@PrID,@LU,@LD)"
            For Each Item As Integer In priv
                param(0) = New SqlParameter("@RoleID", roleid)
                param(1) = New SqlParameter("@PrID", Item)
                param(2) = New SqlParameter("@LU", CCommon.GetUserID)
                param(3) = New SqlParameter("@LD", Date.Now())
                intResult = intResult + dbConnection.ExecuteNonQuery(sql, param)
            Next Item
            Return intResult
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "InsertPrivileges")
            Return 0
        End Try
    End Function

    Public Shared Function DeleteRole(ByVal RoleId As Integer, ByVal dbConnection As dbAccess) As Boolean
        Try
            Return dbConnection.ExecuteNonQuery("DELETE FROM mroles WHERE RoleID = " & RoleId & "")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "DeleteRole")
            Return False
        End Try
    End Function

    Public Shared Function DeleteRolePriv(ByVal RoleId As Integer, ByVal dbConnection As dbAccess) As Boolean
        Try
            Return dbConnection.ExecuteNonQuery("DELETE FROM mrolepriv WHERE RoleID = " & RoleId & "")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DRole", "DeleteRolePriv")
            Return False
        End Try
    End Function

    Public Shared Function DeleteAll(ByVal RoleId As Integer) As Boolean
        Dim dbConnection As New dbAccess
        Try
            dbConnection.BeginTransaction()
            DRole.DeleteRole(RoleId, dbConnection)
            DRole.DeleteRolePriv(RoleId, dbConnection)
            dbConnection.CommitTransaction()
            Return True
        Catch ex As Exception
            dbConnection.RollBackTransaction()
            Return False
        Finally
            dbConnection.closeSqlcon()
        End Try
    End Function
End Class
